<?php
// 连接数据库
include 'connect.php';
// 获取员工总数
$sql_count = "SELECT COUNT(*) AS total FROM employees";
$result_count = mysqli_query($conn, $sql_count);
$row_count = mysqli_fetch_assoc($result_count);
$total = $row_count['total'];


// 获取离职员工总数
$sql_count_leave = "SELECT COUNT(*) AS total_leave FROM employees where status = '离职'";
$result_count_leave = mysqli_query($conn, $sql_count_leave);
$row_count_leave = mysqli_fetch_assoc($result_count_leave);
$total_leave = $row_count_leave['total_leave'];

// 获取在职员工总数
$sql_count_all = "SELECT COUNT(*) AS total_employees FROM employees where status = '在职'";
$result_count_all = mysqli_query($conn, $sql_count_all);
$row_count_all = mysqli_fetch_assoc($result_count_all);
$total_employees = $row_count_all['total_employees'];


// 获取在职男性员工总数
$sql_count_male = "SELECT COUNT(*) AS total_male FROM employees WHERE gender = '男' and status = '在职'";
$result_count_male = mysqli_query($conn, $sql_count_male);
$row_count_male = mysqli_fetch_assoc($result_count_male);
$total_male = $row_count_male['total_male'];

// 获取在职女性员工总数
$sql_count_female = "SELECT COUNT(*) AS total_female FROM employees WHERE gender = '女' and status = '在职'";
$result_count_female = mysqli_query($conn, $sql_count_female);
$row_count_female = mysqli_fetch_assoc($result_count_female);
$total_female = $row_count_female['total_female'];

// 获取今日过生日人数
$currentDate = date('m-d');
$sql_count_today_birthday = "SELECT COUNT(*) AS total_today_birthday FROM employees WHERE DATE_FORMAT(birthday, '%m-%d') = '$currentDate'";
$result_count_today_birthday = mysqli_query($conn, $sql_count_today_birthday);
$row_count_today_birthday = mysqli_fetch_assoc($result_count_today_birthday);
// 获取本月过生日人数
$currentMonth = date('m');
$sql_count_month_birthday = "SELECT COUNT(*) AS total_month_birthday FROM employees WHERE DATE_FORMAT(birthday, '%m') = '$currentMonth'";
$result_count_month_birthday = mysqli_query($conn, $sql_count_month_birthday);
$row_count_month_birthday = mysqli_fetch_assoc($result_count_month_birthday);
// 获取上个月今天以后入职的人员数量
$lastMonthToday = date('Y-m-01', strtotime('-1 month'));
$sql_lastMonth = "SELECT COUNT(*) AS lastMonthCount FROM employees WHERE hire_date > '$lastMonthToday'";
$result_lastMonth = mysqli_query($conn, $sql_lastMonth);
$row_lastMonth = mysqli_fetch_assoc($result_lastMonth);
// 获取两个月前今天以后入职的人员数量
$twoMonthsAgoToday = date('Y-m-01', strtotime('-2 month'));
$sql_twoMonths = "SELECT COUNT(*) AS twoMonthsCount FROM employees WHERE hire_date > '$twoMonthsAgoToday'";
$result_twoMonths = mysqli_query($conn, $sql_twoMonths);
$row_twoMonths = mysqli_fetch_assoc($result_twoMonths);
// 获取六个月前今天以后入职的人员数量
$sixMonthsAgoToday = date('Y-m-01', strtotime('-6 month'));
$sql_sixMonths = "SELECT COUNT(*) AS sixMonthsCount FROM employees WHERE hire_date > '$sixMonthsAgoToday'";
$result_sixMonths = mysqli_query($conn, $sql_sixMonths);
$row_sixMonths = mysqli_fetch_assoc($result_sixMonths);
// 获取最近30天入职人数
$sql_count_recent_hire = "SELECT COUNT(*) AS total_recent_hire FROM employees WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)";
$result_count_recent_hire = mysqli_query($conn, $sql_count_recent_hire);
$row_count_recent_hire = mysqli_fetch_assoc($result_count_recent_hire);
$total_recent_30_hire = $row_count_recent_hire['total_recent_hire'];
//计算不同年限区间的员工数量
$currentDate = date('Y-m-d');
$sql_years_intervals = "SELECT 
            SUM(CASE WHEN TIMESTAMPDIFF(YEAR, hire_date, '$currentDate') > 10 THEN 1 ELSE 0 END) AS more_than_10_years,
            SUM(CASE WHEN TIMESTAMPDIFF(YEAR, hire_date, '$currentDate') BETWEEN 5 AND 10 THEN 1 ELSE 0 END) AS five_to_ten_years,
            SUM(CASE WHEN TIMESTAMPDIFF(YEAR, hire_date, '$currentDate') BETWEEN 3 AND 5 THEN 1 ELSE 0 END) AS three_to_five_years,
            SUM(CASE WHEN TIMESTAMPDIFF(YEAR, hire_date, '$currentDate') BETWEEN 1 AND 3 THEN 1 ELSE 0 END) AS one_to_three_years
        FROM employees";
$result_years_intervals = $conn->query($sql_years_intervals);
$row_years_intervals = $result_years_intervals->fetch_assoc();
$more_than_10_years = $row_years_intervals['more_than_10_years'];
$five_to_ten_years = $row_years_intervals['five_to_ten_years'];
$three_to_five_years = $row_years_intervals['three_to_five_years'];
$one_to_three_years = $row_years_intervals['one_to_three_years'];
//获取男性60岁以上员工数量
$sql_male_60 = "SELECT COUNT(*) AS male_60 FROM employees WHERE gender = 'M' AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) > 60";
$result_male_60 = mysqli_query($conn, $sql_male_60);
$row_male_60 = mysqli_fetch_assoc($result_male_60);
$total_male_60 = $row_male_60['male_60'];
//获取女性50岁以上员工数量
$sql_female_50 = "SELECT COUNT(*) AS female_50 FROM employees WHERE gender = 'F' AND TIMESTAMPDIFF(YEAR, birthday, CURDATE()) > 50";
$result_female_50 = mysqli_query($conn, $sql_female_50);
$row_female_50 = mysqli_fetch_assoc($result_female_50);
$total_female_50 = $row_female_50['female_50'];
// 关闭数据库连接
?>
<!DOCTYPE html>
<html lang="zh">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
    <!-- Favicons -->
    <link rel="icon" href="/favicon.ico">
    <title>员工统计</title>
</head>
<body>
<?php include 'nav.php'; ?>
<div class="container">
    <div class="row mt-3">
        <div class="col-md-2">
            <div class="card">
                <div class="card-body bg-info-subtle">
                    <h5 class="card-title">员工总数</h5>
                    <p class="card-text"><a href="all.php" class="btn btn-success"> <?php echo $total; ?></a>
                    </p>
                </div>
            </div>
        </div>
        <div class="col-md-2">
            <div class="card">
                <div class="card-body bg-info-subtle">
                    <h5 class="card-title">在职总数</h5>
                    <p class="card-text"><a href="all.php" class="btn btn-success"> <?php echo $total_employees; ?></a>
                    </p>
                </div>
            </div>
        </div>
        <div class="col-md-2">
            <div class="card">
                <div class="card-body bg-info-subtle">
                    <h5 class="card-title">女性员工总数</h5>
                    <p class="card-text"><a href='search.php?gender=女' class='btn btn-success'> <?php  echo $total_female; ?></a>
                    </p>
                </div>
            </div>
        </div>
        <div class="col-md-2">
            <div class="card">
                <div class="card-body bg-info-subtle">
                    <h5 class="card-title">男性员工总数</h5>
                    <p class="card-text"><a href="search.php?gender=男" class="btn btn-primary"><?php echo $total_male;?></a>
                    </p>
                </div>
            </div>
        </div>
   </div>
    <div class="row mt-3">
        <div class="col-md-3">
            <div class="card">
                <div class="card-body bg-primary-subtle">
                    <h5 class="card-title">最近30天入职</h5>
                    <p class="card-text"><?php echo $total_recent_30_hire; ?></p>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card">
                <div class="card-body bg-primary-subtle">
                    <h5 class="card-title">最近一个月入职</h5>
                    <p class="card-text"><?php echo $row_lastMonth['lastMonthCount']; ?></p>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card">
                <div class="card-body bg-primary-subtle">
                    <h5 class="card-title">最近二个月入职</h5>
                    <p class="card-text"><?php echo $row_twoMonths['twoMonthsCount']; ?></p>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card">
                <div class="card-body bg-primary-subtle">
                    <h5 class="card-title">最近六个月入职</h5>
                    <p class="card-text"><?php echo $row_sixMonths['sixMonthsCount'] ?></p>
                </div>
            </div>
        </div>
    </div>
    <div class="row mt-3">
        <div class="col-md-2">
            <div class="card">
                <div class="card-body bg-dark-subtle">
                    <h5 class="card-title">十年</h5>
                    <p class="card-text"><a href="search.php?years_interval=more_than_10_years"
                                            class="btn btn-dark"><?php echo $more_than_10_years; ?></a></p>
                </div>
            </div>
        </div>
        <div class="col-md-2">
            <div class="card">
                <div class="card-body bg-dark-subtle">
                    <h5 class="card-title">5-10年</h5>
                    <p class="card-text"><a href="search.php?years_interval=five_to_ten_years"
                                            class="btn btn-dark"><?php echo $five_to_ten_years; ?></a></p>
                </div>
            </div>
        </div>
        <div class="col-md-2">
            <div class="card">
                <div class="card-body bg-dark-subtle">
                    <h5 class="card-title">3-5年</h5>
                    <p class="card-text"><a href="search.php?years_interval=three_to_five_years"
                                            class="btn btn-dark"> <?php echo $three_to_five_years; ?></a></p>
                </div>
            </div>
        </div>
        <div class="col-md-2">
            <div class="card">
                <div class="card-body bg-dark-subtle">
                    <h5 class="card-title">1-3年</h5>
                    <p class="card-text"><a href="search.php?years_interval=one_to_three_years"
                                            class="btn btn-dark"><?php echo $one_to_three_years; ?></a></p>
                </div>
            </div>
        </div>
        <div class="col-md-2">
            <div class="card">
                <div class="card-body bg-dark-subtle">
                    <h5 class="card-title">男性60岁</h5>
                    <p class="card-text"><a href="retire.php?retire=male_60"
                                            class="btn btn-dark"><?php echo $total_male_60; ?></a></p>
                </div>
            </div>
        </div>
        <div class="col-md-2">
            <div class="card">
                <div class="card-body bg-dark-subtle">
                    <h5 class="card-title">女性50岁</h5>
                    <p class="card-text"><a href="retire.php?retire=female_50"
                                            class="btn btn-dark"><?php echo $total_female_50; ?></a></p>
                </div>
            </div>
        </div>
    </div>
    <div class="row mt-3">
        <div class="col-md-3">
            <div class="card">
                <div class="card-body bg-success-subtle">
                    <h5 class="card-title">生日分布</h5>
                    <p class="card-text"><a href="birthday.php" class="btn btn-info"><?php echo $more_than_10_years; ?></a></p>
                </div>
           </div>
        </div>
        <div class="col-md-3">
            <div class="card">
                <div class="card-body bg-success-subtle">
                    <h5 class="card-title">今日过生日人数</h5>
                    <p class="card-text"><a href="today.php"
                                            class="btn btn-warning"><?php echo $row_count_today_birthday['total_today_birthday']; ?> </a>
                    </p>
                </div>
            </div>
        </div>
        <div class="col-md-3">
            <div class="card">
                <div class="card-body bg-success-subtle">
                    <h5 class="card-title">本月过生日人数</h5>
                    <p class="card-text"><a href="month.php"
                                            class="btn btn-info"><?php echo $row_count_month_birthday['total_month_birthday']; ?></a>
                    </p>
                </div>
            </div>
        </div>
    </div>
</body>
</html>